const express = require("express")
const app = express()

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'studentmanage',
    timezone:"SYSTEM"
});

connection.connect();

//跨域请求
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});
//选课管理操作
//删除
app.get("/coursemanage/delete",(req,res)=>{
    let sql = `delete  from tbl_student_course where sid=? and cid=?`;
    let params=req.query;
    let value=[params.StuNoId,params.courseId];
    console.log(sql)
    if(params.StuNoId && params.courseId){
        connection.query(sql,value,function (error, results, fields) {
            if (error) throw error;
            console.log('The solution is: ',results);
            res.status(200).send(results);

        });
    }

})
//保存添加
app.get("/coursemanage/save",(req,res)=>{
    let sql = ` INSERT into tbl_student_course(sid,cid) values `;
    let params=req.query;
    let value=[params.StuNoId];
    let value_cid = params.fsCstuCid;
    console.log(value_cid);
    console.log(req.query);
    if(params.StuNoId && params.fsCstuCid){
        for (var i = 0 ; i < value_cid.length; i++){
            if(i==value_cid.length-1){
                sql+= ` (${value},${value_cid[i]})`
            }else{
                sql+= ` (${value},${value_cid[i]}),`
            }
        }
        console.log(sql);
        connection.query(sql,value,function (error, results, fields) {
            if (error) throw error;
            console.log('The solution is: ',results);
            res.status(200).send(results);

        });
    }else{
        console.log("添加失败");
        res.status(200).send("添加失败..");
    }



})

//查询课程信息
app.get("/coursemanage/querycourse",(req,res)=>{
    let sql = `SELECT * from tbl_course where cid=?`;
    let params=req.query;
    let value=[params.courseId];
    console.log(value)
    console.log("------------ok");
    console.log(sql);
    connection.query(sql,value,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})
//查询学生所学课程
app.get("/coursemanage/queryStuBycourse",(req,res)=>{
    let params=req.query;
    let value=[params.stuNoId];
    console.log(value+"==================================")
    let sql = `select tbl_course.cid,cname,cnum,ctime,cremark from tbl_student_course,tbl_course where tbl_student_course.cid=tbl_course.cid and sid=?`;
    console.log(sql);
    connection.query(sql,value,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})
//查询课程信息
app.get("/coursemanage/selcourse",(req,res)=>{
    let sql = `select cid,cname from tbl_course `;
    console.log(sql);
    connection.query(sql,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})
//根据学号查询获取学生信息
app.get("/coursemanage/selstunoid",(req,res)=>{
    let params=req.query;
    console.log("=============ok")
    let value=[params.StuNoId];
    let sql = `select * from tbl_student where sid=?`;
    console.log(sql);
    connection.query(sql,value,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})
//选课管理操作尾端



//统计总数据
app.get("/courmanagepage",(req, res) => {
    let sql = `SELECT count(*) pgCount FROM tbl_student_course sc inner join tbl_course c ON(sc.cid=c.cId)`;
    connection.query(sql,function (error, results, fields) {
        if (error) throw error;
        res.status(200).send(results);
    });
})
//查询
app.get("/coursemanage",(req,res)=>{
    let sql = `SELECT sid,sc.cid,cname,cnum,ctime,cremark FROM tbl_student_course sc inner join tbl_course c ON(sc.cid=c.cId) where 1=1`;
    let params=req.query;
    let value=[];
    console.log();
    console.log("------------ok");
    if(params.CidAndName){
        value=[params.CidAndName];
        sql+=` and sc.cid=? or cname like '%${value}%'`;
    }
    //存储前台拿到的页面数据
    //每页页数
    value.push(params.Pages);
    console.log(value+"---------------")
    sql+=` limit ${params.Pages},${params.Count}`;
    console.log(sql);
    connection.query(sql,value,function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ',results);
        res.status(200).send(results);

    });
})

app.listen(8003,()=>{
    console.log("Server running at http://localohost:8003/")
})